﻿BEGIN TRY
	BEGIN TRAN

	MERGE [dbo].[Transformation] AS TGT 
	USING
	(
		SELECT	0, -1,	'NN',	'None',					'None',										1 UNION ALL
		SELECT  1,	1,	'NK',	'Natural Key',			'Natural Key',								2 UNION ALL
		SELECT  2,	1,	'SK',	'Surrogate Key',		'Surrogate Key',							3 UNION ALL
		SELECT	7,	2,	'0',	'SCD0',					'SCD Type 0 - Fixed Attribute',				5 UNION ALL
		SELECT	10,	2,	'1',	'SCD1',					'SCD Type 1 - Changing Attribute',			6 UNION ALL
		SELECT	20,	2,	'2',	'SCD2',					'SCD Type 2 - Historical Attribute',		7 UNION ALL
		SELECT	21,	3,	'2F',	'SCD2 From',			'SCD Type 2 - Record Start Timestamp',		11 UNION ALL
		SELECT	22,	3,	'2T',	'SCD2 To',				'SCD Type 2 - Record End Timestamp',		12 UNION ALL
		SELECT	23,	3,	'2A',	'SCD2 Is Active',		'SCD Type 2 - Record Active Indicator',		13 UNION ALL
		SELECT	30,	2,	'3C',	'SCD3 Current',			'SCD Type 3 - Current Attribute Value',		8 UNION ALL
		SELECT	31,	2,	'3O',	'SCD3 Original',		'SCD Type 3 - Original Attribute Value',	9 UNION ALL
		SELECT	32,	2,	'3P',	'SCD3 Previous',		'SCD Type 3 - Previous Attribute Value',	10 UNION ALL
		SELECT	33,	3,	'3F',	'SCD3 From',			'SCD Type 3 - Record From Timestamp',		14 UNION ALL
		SELECT	80,	3,	'CR',	'Created Timestamp',	'Record Created Timestamp',					15 UNION ALL
		SELECT	81,	3,	'MD',	'Modified Timestamp',	'Record Modified Timestamp',				16 UNION ALL
		SELECT	82,	3,	'DL',	'Deleted Timestamp',	'Record Deleted Timestamp',					17 UNION ALL
		SELECT	83,	3,	'AF',	'Is Active',			'Record Active Indicator',					18 UNION ALL
		SELECT	84,	3,	'DF',	'Is Deleted',			'Record Deleted Indicator',					19 UNION ALL
		SELECT	85,	3,	'VR',	'Version',				'Record Version Number',					20 UNION ALL
		SELECT	86,	3,	'HB',	'Hashbytes',			'Record Hash',								21 UNION ALL
		SELECT	87,	3,	'CS',	'Checksum',				'Record Checksum',							22
	) AS SRC ([TransformationId], [TransformationTypeId], [TransformationCode], [TransformationShortName], [TransformationLongName], [SortOrder])
	ON TGT.[TransformationId] = SRC.[TransformationId]  

	WHEN MATCHED THEN   
	UPDATE SET
		[TransformationTypeId] = SRC.[TransformationTypeId],
		[TransformationCode] = SRC.[TransformationCode],
		[TransformationShortName] = SRC.[TransformationShortName],
		[TransformationLongName] = SRC.[TransformationLongName],
		[SortOrder] = SRC.[SortOrder]
	WHEN NOT MATCHED BY TARGET THEN  
		INSERT ([TransformationId], [TransformationTypeId], [TransformationCode], [TransformationShortName], [TransformationLongName], [SortOrder])
		VALUES (SRC.[TransformationId], SRC.[TransformationTypeId], SRC.[TransformationCode], SRC.[TransformationShortName], SRC.[TransformationLongName], SRC.[SortOrder])

	WHEN NOT MATCHED BY SOURCE THEN  
		DELETE;

	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
END CATCH